﻿using BLL;
using Model;
using NPOI.HSSF.UserModel;
using NPOI.SS.Converter;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Text;
using System.Text.RegularExpressions;
using System.Web;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using Utility;

namespace BaseUI
{
    public class BasePage : System.Web.UI.Page
    {
        public string _System_Skin = ConfigurationManager.AppSettings["DefaultSkin"] + "";
        private Sys_ModuleBLL smb = new Sys_ModuleBLL();
        private Sys_RoleModuleBLL srmb = new Sys_RoleModuleBLL();
        private Sys_UserBLL user = new Sys_UserBLL();
        private Sys_DepartmentBLL sdb = new Sys_DepartmentBLL();

        private string ExcelWay = ConfigurationManager.ConnectionStrings["ExcelWay"].ConnectionString;
        public Sys_User lognUser;

        #region 用户信息

        /// <summary>
        /// 登录用户的用户名
        /// </summary>
        public string CurrentUserName
        {
            get
            {
                if (lognUser == null)
                {
                    return null;
                }
                return lognUser.USER_NAME;
            }
        }

        /// <summary>
        /// 登录用户的用户ID
        /// </summary>
        public decimal CurrentUserID
        {
            get
            {
                return lognUser.USER_ID;
            }
        }

        /// <summary>
        /// 登录用户的公司ID
        /// </summary>
        public decimal CurrentUserCompanyID
        {
            get
            {
                return lognUser.USER_COMPANY;
            }
        }

        /// <summary>
        /// 登录用户的职位信息
        /// </summary>
        public decimal CurrentUserDeptID
        {
            get
            {
                return lognUser.USER_DEPARTMENT;
            }
        }

        /// <summary>
        /// 登录用户的公司名称
        /// </summary>
        public string CurrentUserCompanyName
        {
            get
            {
                return new Sys_CompanyBLL().GetInfoById(CurrentUserCompanyID.ToString()).CMY_NAME;
            }
        }

        #endregion 用户信息

        public BasePage()
        {
            this.Load += new EventHandler(BasePage_Load);
        }

        /// <summary>
        /// 页面初始化加载事件
        /// </summary>
        /// <param name="e"></param>
        protected override void OnPreInit(EventArgs e)
        {
            base.OnPreInit(e);
            LoadHeader();
        }

        /// <summary>
        /// 初始化页面
        /// </summary>
        protected virtual void LoadHeader()
        {
            Session["SYSTEM_SKIN"] = _System_Skin;
            Page.Theme = _System_Skin;

            if (!Page.ClientScript.IsClientScriptIncludeRegistered("jquery"))
            {
                Page.ClientScript.RegisterClientScriptInclude(this.Page.GetType(), "jquery", "/Scripts/jquery-1.7.min.js");
            }

            if (!Page.ClientScript.IsClientScriptIncludeRegistered("MyScript"))
            {
                Page.ClientScript.RegisterClientScriptInclude("MyScript", "/Scripts/MyScript.js");
            }

            if (!Page.ClientScript.IsClientScriptIncludeRegistered("ScriptsConfigs"))
            {
                Page.ClientScript.RegisterClientScriptInclude("ScriptsConfigs", "/App_Themes/MSN_Blue/configs.js");
            }

            if (!Page.ClientScript.IsClientScriptIncludeRegistered("ProcessingWindow"))
            {
                Page.ClientScript.RegisterClientScriptInclude("ProcessingWindow", "/Scripts/Main_JS/ProcessingWindow.js");
            }
        }

        protected virtual void BasePage_Load(object sender, EventArgs e)
        {
            if (!ExistSession())
            {
                ReLogin();
            }
            HtmlGenericControl myCss = new HtmlGenericControl();
            myCss.TagName = "link";
            myCss.Attributes.Add("type", "text/css");
            myCss.Attributes.Add("rel", "stylesheet");
            myCss.Attributes.Add("href", ResolveUrl(Page.ResolveClientUrl("/CSS/Main.css")));
            Page.Header.Controls.AddAt(0, myCss);
            if (!IsPostBack)
            {
                Session["SysUserID"] = CurrentUserID;
                //加载页面按钮权限
                AuthPage();
            }
        }

        /// <summary>
        /// 页面按钮权限加载事件
        /// </summary>
        protected virtual void AuthPage()
        {
            if (lognUser.USER_LOGINNAME.ToUpper() != "SUPER")
            {
                #region 获取页面按钮权限信息

                string urlStr = Request.RawUrl.ToLower();
                if (!urlStr.Contains("left.aspx") && !urlStr.Contains("right.aspx") && !urlStr.Contains("authority.aspx"))
                {
                    urlStr = urlStr.Replace("/", "\\");
                    string[] str = urlStr.Split('\\');
                    string pageName = str[str.Length - 1].ToString();

                    string[] str1 = pageName.Split('?');
                    string realName = str1[0].ToString();
                    /*
                    DataTable dt = srmb.findByRoleAndModule(realName, user.USER_ROLEID.ToString());
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            ServiceControl(Page, dt.Rows[i]["BUTTONS"].ToString());
                        }
                    }
                    */
                }

                #endregion 获取页面按钮权限信息
            }
        }

        private void ServiceControl(Page page, string buttonStr)
        {
            /*
            List<Button> allControls = new List<Button>();
            GetControlList<Button>(Page.Controls, allControls);

            foreach (Button btn in allControls)
            {
                string btnName = btn.Text;

                if (!buttonStr.Contains(btnName) && btn.Text != "首页" && btn.Text != "上一页" && btn.Text != "下一页" && btn.Text != "末页" && btn.Text != "跳转")
                {
                    btn.Visible = false;
                }
                else
                {
                    btn.Visible = true;
                }
            }
            */
        }

        public void GetControlList<T>(ControlCollection controlCollection, List<T> resultCollection)
        where T : Control
        {
            foreach (Control control in controlCollection)
            {
                if (control is T)
                    resultCollection.Add((T)control);

                if (control.HasControls())
                    GetControlList(control.Controls, resultCollection);
            }
        }

        public void DisabledBackspace()
        {
            Response.Buffer = true;
            Response.ExpiresAbsolute = DateTime.Now.AddSeconds(-1);
            Response.Expires = 0;
            Response.CacheControl = "no-cache";
            Response.AddHeader("Pragma", "No-Cache");
        }

        /// <summary>
        /// 检测Session是否存在
        /// </summary>
        /// <returns></returns>
        public bool ExistSession()
        {
            if (Session["SysUserInfo"] == null)
            {
                //获取Cookie当中存储的用户ID,用户登录策略
                HttpCookie cookie = Request.Cookies["USER_KEY"];
                if (cookie == null)
                {
                    return false;
                }
                if (cookie.Value == "")
                {
                    return false;
                }
                try
                {
                    var str = DESEncrypt.Decrypt(cookie.Value).Split('|');
                    if (str.Length == 2)
                    {
                        var exdate = Convert.ToDateTime(str[1]);
                        if (exdate > DateTime.Now)
                        {
                            var entity = user.Find(Convert.ToDecimal(DESEncrypt.Decrypt(cookie.Value).Split('|')[0]));
                            if (entity != null)
                            {
                                Session["SysUserInfo"] = entity;
                                lognUser = entity;
                                return true;
                            }
                        }
                    }
                    cookie.Value = "";
                    cookie.Expires = DateTime.Now.AddHours(-1);
                    Request.Cookies.Add(cookie);
                    return false;
                }
                catch (Exception)
                {
                    Request.Cookies.Remove("USER_KEY");
                    return false;
                }
            }
            else
            {
                lognUser = (Sys_User)System.Web.HttpContext.Current.Session["SysUserInfo"];
                return true;
            }
        }

        /// <summary>
        /// 跳转到登录界面
        /// </summary>
        public void ReLogin()
        {
            var msg = "未登录或者登录已失效!";
            StringBuilder Builder = new StringBuilder();
            Builder.AppendLine("<script type=\"text/javascript\">");
            Builder.AppendLine("    if(typeof top.MessageBox != \"undefined\"){");
            Builder.AppendLine("        top.location.href=\"/Login.html\";");
            Builder.AppendLine("    }");
            Builder.AppendLine("    else{");
            Builder.AppendLine("        location.href=\"/Login.html\";");
            Builder.AppendLine("    }");
            Builder.AppendLine("</script>");

            Response.Write(Builder.ToString());
            Response.End();
        }

        /// <summary>
        /// 获取用户名-字符串
        /// </summary>
        /// <param name="ids">用户ID字符串（用‘,’区分）</param>
        /// <returns></returns>
        public string GetUserNames(string ids)
        {
            string result = "";
            if (!string.IsNullOrEmpty(ids))
            {
                DataTable dt = user.GetDataUser(ids);

                if (dt != null && dt.Rows.Count > 0)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        result += dr["USER_NAME"].ToString() + ",";
                    }
                }

                try
                {
                    result = result.Remove(result.Length - 1);
                }
                catch
                {
                    return result;
                }
            }
            return result;
        }

        /// <summary>
        /// 获取权限名称-字符串
        /// </summary>
        /// <param name="ids">权限ID字符串（用‘,’区分）</param>
        /// <returns></returns>
        public string GetRoleNames(string ids)
        {
            if (ids == "")
            {
                return "";
            }
            return new Sys_RoleBLL().GetRoleNames(ids);
        }

        #region 导出Excel

        private int startrow = 0;

        public override void VerifyRenderingInServerForm(Control control)
        {
        }

        /// <summary>
        /// 导出方法
        /// </summary>
        /// <param name="objControl"></param>
        /// <param name="strFileName"></param>
        public void toExecl(System.Web.UI.WebControls.WebControl objControl, string strFileName)
        {
            strFileName = System.Web.HttpUtility.UrlEncode(strFileName, System.Text.Encoding.UTF8);
            System.Web.HttpContext.Current.Response.Clear();
            System.Web.HttpContext.Current.Response.ContentType = "application/octet-stream";
            System.Web.HttpContext.Current.Response.Buffer = true;
            System.Web.HttpContext.Current.Response.Charset = "gb2312";
            System.Web.HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment; filename=" + strFileName + ".xls");
            System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
            System.Web.HttpContext.Current.Response.ContentType = "application/ms-excel";

            System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("zh-CN", true);
            System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad);
            System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);

            objControl.RenderControl(oHtmlTextWriter);

            objControl = null;
            System.Web.HttpContext.Current.Response.Write(oStringWriter.ToString().Replace("<td", "<td STYLE='MSO-NUMBER-FORMAT:\\@'"));
            System.Web.HttpContext.Current.Response.Buffer = false;
            System.Web.HttpContext.Current.Response.Flush();
            System.Web.HttpContext.Current.Response.End();
        }

        /// <summary>
        /// 票据导出
        /// </summary>
        /// <param name="BankName">开户银行</param>
        /// <param name="Account">账号</param>
        /// <param name="dts">数据</param>
        /// <param name="Name">文件名</param>
        /// <param name="HeadTitle">头部文件名</param>
        /// <returns></returns>
        public static string ToExecl(string BankName, string Account, DataTable dts, string Name, string HeadTitle)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();//npoi对象
            ISheet sheet = workbook.CreateSheet(Name);
            //文件夹相对路径
            string directoryPath = string.Format("/{0}/{1}/{2}/{3}/", "Content", "Excels", DateTime.Now.ToString("yyyy"), DateTime.Now.ToString("MM"));
            //文件名（带后缀名）
            string fileName = Name + DateTime.Now.ToString("yyyy年MM月dd日hh时mm分ss秒") + ".xls";
            //文件相对路径
            string filePath = directoryPath + fileName;
            //判断目录是否存在
            string absDirPath = System.Web.HttpContext.Current.Server.MapPath(directoryPath);
            if (!System.IO.Directory.Exists(absDirPath))
            {
                System.IO.Directory.CreateDirectory(absDirPath);
            }

            #region 列做表头弃用

            DataTable dt = new DataTable();
            dt.Columns.Add(new DataColumn("序号", typeof(string)));
            dt.Columns.Add(new DataColumn("票据种类", typeof(string)));
            dt.Columns.Add(new DataColumn("购入情况", typeof(string)));
            dt.Columns.Add(new DataColumn("日期", typeof(string)));
            dt.Columns.Add(new DataColumn("票据号", typeof(string)));
            dt.Columns.Add(new DataColumn("份数", typeof(string)));
            dt.Columns.Add(new DataColumn("使用情况", typeof(string)));
            dt.Columns.Add(new DataColumn("出票日期", typeof(string)));
            dt.Columns.Add(new DataColumn("票据期限（月）", typeof(string)));
            dt.Columns.Add(new DataColumn("到日期", typeof(string)));
            dt.Columns.Add(new DataColumn("票据号码", typeof(string)));
            dt.Columns.Add(new DataColumn("收款单位", typeof(string)));
            dt.Columns.Add(new DataColumn("用途", typeof(string)));
            dt.Columns.Add(new DataColumn("项目名称", typeof(string)));
            dt.Columns.Add(new DataColumn("合同编号", typeof(string)));
            dt.Columns.Add(new DataColumn("金额（元）", typeof(string)));
            dt.Columns.Add(new DataColumn("经办人", typeof(string)));
            dt.Columns.Add(new DataColumn("收票时间", typeof(string)));
            dt.Columns.Add(new DataColumn("收票人签字", typeof(string)));
            dt.Columns.Add(new DataColumn("收票人联系电话", typeof(string)));
            dt.Columns.Add(new DataColumn("承兑时间", typeof(string)));

            #endregion 列做表头弃用

            #region 数据列

            DataTable dt_ = new DataTable();
            dt_.Columns.Add(new DataColumn("序号", typeof(string)));
            dt_.Columns.Add(new DataColumn("票据种类", typeof(string)));
            dt_.Columns.Add(new DataColumn("日期", typeof(string)));
            dt_.Columns.Add(new DataColumn("票据号", typeof(string)));
            dt_.Columns.Add(new DataColumn("份数", typeof(string)));
            dt_.Columns.Add(new DataColumn("出票日期", typeof(string)));
            dt_.Columns.Add(new DataColumn("票据期限（月）", typeof(string)));
            dt_.Columns.Add(new DataColumn("到日期", typeof(string)));
            dt_.Columns.Add(new DataColumn("票据号码", typeof(string)));
            dt_.Columns.Add(new DataColumn("收款单位", typeof(string)));
            dt_.Columns.Add(new DataColumn("项目名称", typeof(string)));
            dt_.Columns.Add(new DataColumn("合同编号", typeof(string)));
            dt_.Columns.Add(new DataColumn("金额（元）", typeof(string)));
            dt_.Columns.Add(new DataColumn("经办人", typeof(string)));
            dt_.Columns.Add(new DataColumn("收票时间", typeof(string)));
            dt_.Columns.Add(new DataColumn("收票人签字", typeof(string)));
            dt_.Columns.Add(new DataColumn("收票人联系电话", typeof(string)));
            dt_.Columns.Add(new DataColumn("承兑时间", typeof(string)));

            #endregion 数据列

            #region 行及样式

            ICellStyle cellstyle = workbook.CreateCellStyle();//设置垂直居中格式
            cellstyle.VerticalAlignment = VerticalAlignment.Center;//垂直居中
            cellstyle.Alignment = HorizontalAlignment.Center;//水平居中

            //头部
            NPOI.SS.UserModel.IRow rows0 = sheet.CreateRow(0);
            //开户银行
            NPOI.SS.UserModel.IRow rows2 = sheet.CreateRow(4);
            //表头
            NPOI.SS.UserModel.IRow row0 = sheet.CreateRow(6);
            NPOI.SS.UserModel.IRow row1 = sheet.CreateRow(7);
            NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(8);

            #endregion 行及样式

            #region 头部

            sheet.AddMergedRegion(new CellRangeAddress(0, 1, 0, 0));
            sheet.AddMergedRegion(new CellRangeAddress(0, 1, 1, 17));
            rows0.CreateCell(1).SetCellValue(HeadTitle); rows0.GetCell(1).CellStyle = cellstyle;

            #endregion 头部

            #region 空行

            sheet.AddMergedRegion(new CellRangeAddress(2, 3, 0, 17));

            #endregion 空行

            #region 构建开户与账号

            sheet.AddMergedRegion(new CellRangeAddress(4, 5, 0, 0));
            sheet.AddMergedRegion(new CellRangeAddress(4, 5, 1, 2));
            sheet.AddMergedRegion(new CellRangeAddress(4, 5, 3, 7));
            sheet.AddMergedRegion(new CellRangeAddress(4, 5, 8, 9));
            sheet.AddMergedRegion(new CellRangeAddress(4, 5, 10, 17));

            rows2.CreateCell(1).SetCellValue("开户银行："); rows2.GetCell(1).CellStyle = cellstyle;
            rows2.CreateCell(3).SetCellValue(BankName); rows2.GetCell(3).CellStyle = cellstyle;
            rows2.CreateCell(8).SetCellValue("账号："); rows2.GetCell(8).CellStyle = cellstyle;
            rows2.CreateCell(10).SetCellValue(Account); rows2.GetCell(10).CellStyle = cellstyle;

            #endregion 构建开户与账号

            #region 复杂表头

            //构建表头
            for (int i = 0; i < dt.Columns.Count; i++)//自适应列
            {
                if (i < 2)
                {
                    sheet.AddMergedRegion(new CellRangeAddress(6, 8, i, i));
                }
                if (i == 2)
                {
                    sheet.AddMergedRegion(new CellRangeAddress(6, 6, i, 4));
                    for (int k = 0; k < 3; k++)
                    {
                        int num = k + i;

                        sheet.AddMergedRegion(new CellRangeAddress(7, 8, num, num));
                    }
                    i = 5;
                }
                if (i == 5)
                {
                    sheet.AddMergedRegion(new CellRangeAddress(6, 6, i, 16));
                    for (int k = 0; k < 12; k++)
                    {
                        int num = i + k;

                        if (k == 5)
                        {
                            sheet.AddMergedRegion(new CellRangeAddress(7, 7, num, num + 1));
                            for (int j = 0; j < 2; j++)
                            {
                                int numint = num + j;
                                sheet.AddMergedRegion(new CellRangeAddress(8, 8, num, num));
                            }
                            k = 6;
                        }
                        else
                        {
                            sheet.AddMergedRegion(new CellRangeAddress(7, 8, num, num));
                        }
                    }
                    i = 17;
                }
                if (i == 17)
                {
                    sheet.AddMergedRegion(new CellRangeAddress(6, 8, i, i));
                }
            }
            //第一行
            row0.CreateCell(0).SetCellValue(dt.Columns[0].ColumnName); row0.GetCell(0).CellStyle = cellstyle;
            row0.CreateCell(1).SetCellValue(dt.Columns[1].ColumnName); row0.GetCell(1).CellStyle = cellstyle;
            row0.CreateCell(2).SetCellValue(dt.Columns[2].ColumnName); row0.GetCell(2).CellStyle = cellstyle;
            row0.CreateCell(5).SetCellValue(dt.Columns[6].ColumnName); row0.GetCell(5).CellStyle = cellstyle;
            row0.CreateCell(17).SetCellValue(dt.Columns[20].ColumnName); row0.GetCell(17).CellStyle = cellstyle;

            //第二行
            row1.CreateCell(2).SetCellValue(dt.Columns[3].ColumnName); row1.GetCell(2).CellStyle = cellstyle;
            row1.CreateCell(3).SetCellValue(dt.Columns[4].ColumnName); row1.GetCell(3).CellStyle = cellstyle;
            row1.CreateCell(4).SetCellValue(dt.Columns[5].ColumnName); row1.GetCell(4).CellStyle = cellstyle;
            //row1.CreateCell(5).SetCellValue(dt.Columns[6].ColumnName); row1.GetCell(5).CellStyle = cellstyle;
            row1.CreateCell(5).SetCellValue(dt.Columns[7].ColumnName); row1.GetCell(5).CellStyle = cellstyle;
            row1.CreateCell(6).SetCellValue(dt.Columns[8].ColumnName); row1.GetCell(6).CellStyle = cellstyle;
            row1.CreateCell(7).SetCellValue(dt.Columns[9].ColumnName); row1.GetCell(7).CellStyle = cellstyle;
            row1.CreateCell(8).SetCellValue(dt.Columns[10].ColumnName); row1.GetCell(8).CellStyle = cellstyle;
            row1.CreateCell(9).SetCellValue(dt.Columns[11].ColumnName); row1.GetCell(9).CellStyle = cellstyle;
            row1.CreateCell(10).SetCellValue(dt.Columns[12].ColumnName); row1.GetCell(10).CellStyle = cellstyle;
            row1.CreateCell(12).SetCellValue(dt.Columns[15].ColumnName); row1.GetCell(12).CellStyle = cellstyle;
            row1.CreateCell(13).SetCellValue(dt.Columns[16].ColumnName); row1.GetCell(13).CellStyle = cellstyle;
            row1.CreateCell(14).SetCellValue(dt.Columns[17].ColumnName); row1.GetCell(14).CellStyle = cellstyle;
            row1.CreateCell(15).SetCellValue(dt.Columns[18].ColumnName); row1.GetCell(15).CellStyle = cellstyle;
            row1.CreateCell(16).SetCellValue(dt.Columns[19].ColumnName); row1.GetCell(16).CellStyle = cellstyle;

            //第三行
            row2.CreateCell(10).SetCellValue(dt.Columns[13].ColumnName); row2.GetCell(10).CellStyle = cellstyle;
            row2.CreateCell(11).SetCellValue(dt.Columns[14].ColumnName); row2.GetCell(11).CellStyle = cellstyle;

            #endregion 复杂表头

            #region 数据写入

            for (int i = 0; i < dts.Rows.Count; i++)
            {
                NPOI.SS.UserModel.IRow rowtemp = sheet.CreateRow(i + 9);
                for (int j = 0; j < dt_.Columns.Count; j++)
                {
                    rowtemp.CreateCell(j).SetCellValue(dts.Rows[i][j].ToString().Trim());
                }
            }

            #endregion 数据写入

            //////生成文件
            MemoryStream msfile = new MemoryStream();
            workbook.Write(msfile);
            System.IO.File.WriteAllBytes(System.Web.HttpContext.Current.Server.MapPath(filePath), msfile.ToArray());
            filePath = filePath.Trim();
            return filePath;
        }

        public void toExeclBill(DataTable dt, string strFileName)
        {
            strFileName = System.Web.HttpUtility.UrlEncode(strFileName, System.Text.Encoding.UTF8);
            var buder = new System.Text.StringBuilder();
            buder.Append(@"<table width='2035' border='0' cellpadding='0' cellspacing='0' style='width: 1526.25pt;
        border-collapse: collapse; table-layout: fixed;'>
        <tbody>
            <tr height='40' style='height: 30.00pt; mso-height-source: userset; mso-height-alt: 600;'>
                <td class='xl70' height='40' width=1945' colspan='17' style='height: 30.00pt; width: 1458.75pt;
                    border-right: none; border-bottom: none;' x:str=''>
                    银行票据台账
                </td>
                <td class='xl67' width='90' style='width: 67.50pt;'>
                </td>
            </tr>
            <tr height='40' style='height: 30.00pt; mso-height-source: userset; mso-height-alt: 600;'>
                <td class='xl67' height='40' colspan='2' style='height: 30.00pt; mso-ignore: colspan;'>
                </td>
                <td class='xl71' colspan='3' style='border-right: none; border-bottom: none;'>
                </td>
                <td class='xl72'>
                </td>
                <td class='xl73'>
                </td>
                <td class='xl72' colspan='5' style='mso-ignore: colspan;'>
                </td>
                <td class='xl80'>
                </td>
                <td class='xl71' colspan='5' style='border-right: none; border-bottom: none;'>
                </td>
            </tr>
            <tr height='40' class='xl65' style='height: 30.00pt; mso-height-source: userset;
                mso-height-alt: 600;'>
                <td class='xl65' height='40' style='height: 30.00pt;'>
                </td>
                <td class='xl74' colspan='16' style='border-right: none; border-bottom: .5pt solid windowtext;'
                    x:str="">
                    开户行：中国建设银行股份有限公司成都第二支行<span style='mso-spacerun: yes;'>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                    </span>账号：51050142620800001209<span style='mso-spacerun: yes;'>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span>
                </td>
                <td class='xl65'>
                </td>
            </tr>");

            buder.Append("<tr>");
            foreach (DataColumn item in dt.Columns)
            {
                buder.Append("<th>" + item.ColumnName + "</th>");
            }
            buder.Append("</tr>");

            #region 注释

            //            buder.Append(@"
            //                        <tr height='40' class='xl66' style='height: 30.00pt; mso-height-source: userset;
            //                            mso-height-alt: 600;'>
            //                            <td class='xl75' height='120' rowspan='3' style='height: 90.00pt; border-right: .5pt solid windowtext;
            //                                border-bottom: .5pt solid windowtext;' x:str=''>
            //                                序号
            //                            </td>
            //                            <td class='xl75' rowspan='3' style='border-right: .5pt solid windowtext; border-bottom: .5pt solid windowtext;'
            //                                x:str=''>
            //                                票据种类
            //                            </td>
            //                            <td class='xl75' colspan='3' style='border-right: .5pt solid windowtext; border-bottom: .5pt solid windowtext;'
            //                                x:str=''>
            //                                购入情况
            //                            </td>
            //                            <td class='xl75' colspan='12' style='border-right: .5pt solid windowtext; border-bottom: .5pt solid windowtext;'
            //                                x:str=''>
            //                                使用情况
            //                            </td>
            //                            <td class='xl75' rowspan='3' style='border-right: .5pt solid windowtext; border-bottom: .5pt solid windowtext;'
            //                                x:str=''>
            //                                承兑时间
            //                            </td>
            //                        </tr>
            //                        <tr height='40' class='xl66' style='height: 30.00pt; mso-height-source: userset;
            //                            mso-height-alt: 600;'>
            //                            <td class='xl75' rowspan='2' style='border-right: .5pt solid windowtext; border-bottom: .5pt solid windowtext;'
            //                                x:str=''>
            //                                日<span style='mso-spacerun: yes;'>&nbsp; </span>期
            //                            </td>
            //                            <td class='xl75' rowspan='2' style='border-right: .5pt solid windowtext; border-bottom: .5pt solid windowtext;'
            //                                x:str=''>
            //                                票据号码
            //                            </td>
            //                            <td class='xl75' rowspan='2' style='border-right: .5pt solid windowtext; border-bottom: .5pt solid windowtext;'
            //                                x:str=''>
            //                                份数
            //                            </td>
            //                            <td class='xl75' rowspan='2' style='border-right: .5pt solid windowtext; border-bottom: .5pt solid windowtext;'
            //                                x:str=''>
            //                                出票日期
            //                            </td>
            //                            <td class='xl76' rowspan='2' style='border-right: .5pt solid windowtext; border-bottom: .5pt solid windowtext;'
            //                                x:str=''>
            //                                票据期限（月）
            //                            </td>
            //                            <td class='xl75' rowspan='2' style='border-right: .5pt solid windowtext; border-bottom: .5pt solid windowtext;'
            //                                x:str=''>
            //                                到期日
            //                            </td>
            //                            <td class='xl75' rowspan='2' style='border-right: .5pt solid windowtext; border-bottom: .5pt solid windowtext;'
            //                                x:str=''>
            //                                票据号码
            //                            </td>
            //                            <td class='xl75' rowspan='2' style='border-right: .5pt solid windowtext; border-bottom: .5pt solid windowtext;'
            //                                x:str=''>
            //                                收款单位
            //                            </td>
            //                            <td class='xl75' colspan='2' style='border-right: .5pt solid windowtext; border-bottom: .5pt solid windowtext;'
            //                                x:str=''>
            //                                用<span style='mso-spacerun: yes;'>&nbsp; </span>途
            //                            </td>
            //                            <td class='xl81' rowspan='2' style='border-right: .5pt solid windowtext; border-bottom: .5pt solid windowtext;'
            //                                x:str=''>
            //                                金额(元）
            //                            </td>
            //                            <td class='xl82' rowspan='2' style='border-right: .5pt solid windowtext; border-bottom: .5pt solid windowtext;'
            //                                x:str=''>
            //                                经办人
            //                            </td>
            //                            <td class='xl82' rowspan='2' style='border-right: .5pt solid windowtext; border-bottom: .5pt solid windowtext;'
            //                                x:str=''>
            //                                收票时间
            //                            </td>
            //                            <td class='xl82' rowspan='2' style='border-right: .5pt solid windowtext; border-bottom: .5pt solid windowtext;'
            //                                x:str=''>
            //                                收票人签字
            //                            </td>
            //                            <td class='xl75' rowspan='2' style='border-right: .5pt solid windowtext; border-bottom: .5pt solid windowtext;'
            //                                x:str=''>
            //                                收票人联系电话
            //                            </td>
            //                        </tr>
            //                        <tr height='40' class='xl66' style='height: 30.00pt; mso-height-source: userset;
            //                            mso-height-alt: 600;'>
            //                            <td class='xl75' x:str=''>
            //                                项目名称
            //                            </td>
            //                            <td class='xl75' x:str=''>
            //                                合同编号
            //                            </td>
            //                        </tr>");

            #endregion 注释

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (i % 2 == 0)
                {
                    buder.Append("<tr height='40' style='height: 30.00pt; mso-height-source: userset; mso-height-alt: 600;'>");
                }
                else
                {
                    buder.Append("<tr height='40' style='background-color: #EAFFFF; height: 30.00pt; mso-height-source: userset; mso-height-alt: 600;'>");
                }
                foreach (DataColumn item in dt.Columns)
                {
                    buder.Append("<td>" + dt.Rows[i][item.ColumnName].ToString() + "</td>");
                }
                buder.Append("</tr>");
            }
            buder.Append(@"</tbody>
    </table>");
            System.Web.HttpContext.Current.Response.Clear();
            System.Web.HttpContext.Current.Response.ContentType = "application/octet-stream";
            System.Web.HttpContext.Current.Response.Buffer = true;
            System.Web.HttpContext.Current.Response.Charset = "gb2312";
            System.Web.HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment; filename=" + strFileName + ".xls");
            System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
            System.Web.HttpContext.Current.Response.ContentType = "application/ms-excel";
            System.Web.HttpContext.Current.Response.Write(buder.ToString());
            System.Web.HttpContext.Current.Response.Buffer = false;
            System.Web.HttpContext.Current.Response.Flush();
            System.Web.HttpContext.Current.Response.End();
        }

        public void toExecl1(DataTable dt, string strFileName)
        {
            strFileName = System.Web.HttpUtility.UrlEncode(strFileName, System.Text.Encoding.UTF8);
            var buder = new System.Text.StringBuilder();
            buder.Append(@"<table class='cont_list' cellspacing='0' rules='all' border='1' style='width: 100%;
    border-collapse: collapse;'>
    <tr>");
            foreach (DataColumn item in dt.Columns)
            {
                buder.Append("<th>" + item.ColumnName + "</th>");
            }
            buder.Append("</tr>");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                buder.Append("<tr>");

                foreach (DataColumn item in dt.Columns)
                {
                    if (i % 2 == 0)
                    {
                        buder.Append("<td style=\"background-color: #EAFFFF;\">" + dt.Rows[i][item.ColumnName].ToString() + "</td>");
                    }
                    else
                    {
                        buder.Append("<td>" + dt.Rows[i][item.ColumnName].ToString() + "</td>");
                    }
                }

                buder.Append("</tr>");
            }
            buder.Append("</table>");
            System.Web.HttpContext.Current.Response.Clear();
            System.Web.HttpContext.Current.Response.ContentType = "application/octet-stream";
            System.Web.HttpContext.Current.Response.Buffer = true;
            System.Web.HttpContext.Current.Response.Charset = "gb2312";
            System.Web.HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment; filename=" + strFileName + ".xls");
            System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
            System.Web.HttpContext.Current.Response.ContentType = "application/ms-excel";
            System.Web.HttpContext.Current.Response.Write(buder.ToString());
            System.Web.HttpContext.Current.Response.Buffer = false;
            System.Web.HttpContext.Current.Response.Flush();
            System.Web.HttpContext.Current.Response.End();
        }

        public void toExecl1(System.Web.UI.WebControls.WebControl objControl, string strFileName)
        {
            //bool isFireFox = false;
            //if (Request.ServerVariables["http_user_agent"].ToLower().IndexOf("firefox") != -1 || Request.ServerVariables["http_user_agent"].ToLower().IndexOf("360") != -1)
            //{
            //    isFireFox = true;
            //}
            //else
            //{
            strFileName = System.Web.HttpUtility.UrlEncode(strFileName, System.Text.Encoding.UTF8);
            //}

            System.Web.HttpContext.Current.Response.Clear();
            System.Web.HttpContext.Current.Response.ContentType = "application/octet-stream";
            System.Web.HttpContext.Current.Response.Buffer = true;
            System.Web.HttpContext.Current.Response.Charset = "utf-8";
            System.Web.HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment; filename=" + strFileName + ".xls");
            System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
            System.Web.HttpContext.Current.Response.ContentType = "application/ms-excel";

            if (System.Web.HttpContext.Current.Request.Cookies["Load"] == null)
            {
                HttpCookie cok = new HttpCookie("Load", "start");
                cok.Path = "/";
                System.Web.HttpContext.Current.Response.AppendCookie(cok);
            }
            else
            {
                System.Web.HttpContext.Current.Request.Cookies["Load"].Value = "start";
                System.Web.HttpContext.Current.Request.Cookies["Load"].Path = "/";
                System.Web.HttpContext.Current.Response.AppendCookie(System.Web.HttpContext.Current.Request.Cookies["Load"]);
            }

            System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("zh-CN", true);
            System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad);
            System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);

            objControl.RenderControl(oHtmlTextWriter);
            objControl = null;
            var html = "<html xmlns:x=\"urn:schemas-microsoft-com:office:excel\">";
            html += "<head>";
            html += "<META http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\">";
            string fileCss = Server.MapPath("~/Css/main.css");
            string cssText = string.Empty;
            StreamReader sr = new StreamReader(fileCss);
            var line = string.Empty;
            while ((line = sr.ReadLine()) != null)
            {
                cssText += line;
            }
            sr.Close();
            html += "<style>" + cssText + "</style>";
            html += "<!--[if gte mso 9]><xml>";
            html += "<x:ExcelWorkbook>";
            html += "<x:ExcelWorksheets>";
            html += "<x:ExcelWorksheet>";
            html += "<x:Name>Report Data</x:Name>";
            html += "<x:WorksheetOptions>";
            html += "<x:Print>";
            html += "<x:ValidPrinterInfo/>";
            html += "</x:Print>";
            html += "</x:WorksheetOptions>";
            html += "</x:ExcelWorksheet>";
            html += "</x:ExcelWorksheets>";
            html += "</x:ExcelWorkbook>";
            html += "</xml>";
            html += "<![endif]--> ";
            
            //var _html = Regex.Replace(oStringWriter.ToString(), @"<script[^>]*?>.*?</script>", "", RegexOptions.IgnoreCase);
            //_html = Regex.Replace(oStringWriter.ToString(), @"<link href=\' rel='stylesheet' type=\'text/css' />", "", RegexOptions.IgnoreCase);
            //_html =
            html += oStringWriter.ToString();
            //string html = oStringWriter.ToString();
            System.Web.HttpContext.Current.Response.Write(html);
            System.Web.HttpContext.Current.Response.Buffer = false;

            System.Web.HttpContext.Current.Request.Cookies["Load"].Value = "end";
            System.Web.HttpContext.Current.Request.Cookies["Load"].Path = "/";
            System.Web.HttpContext.Current.Response.AppendCookie(System.Web.HttpContext.Current.Request.Cookies["Load"]);

            System.Web.HttpContext.Current.Response.Flush();
            System.Web.HttpContext.Current.Response.End();
        }

        public void toExecl1(string htmlStr, string strFileName)
        {
            bool isFireFox = false;
            if (Request.ServerVariables["http_user_agent"].ToLower().IndexOf("firefox") != -1 || Request.ServerVariables["http_user_agent"].ToLower().IndexOf("360") != -1)
            {
                isFireFox = true;
            }
            else
            {
                strFileName = System.Web.HttpUtility.UrlEncode(strFileName, System.Text.Encoding.UTF8);
            }

            System.Web.HttpContext.Current.Response.Clear();
            System.Web.HttpContext.Current.Response.ContentType = "application/octet-stream";
            System.Web.HttpContext.Current.Response.Buffer = true;
            System.Web.HttpContext.Current.Response.Charset = "gb2312";
            System.Web.HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment; filename=" + strFileName + ".xls");
            System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
            System.Web.HttpContext.Current.Response.ContentType = "application/ms-excel";

            if (System.Web.HttpContext.Current.Request.Cookies["Load"] == null)
            {
                HttpCookie cok = new HttpCookie("Load", "start");
                cok.Path = "/";
                System.Web.HttpContext.Current.Response.AppendCookie(cok);
            }
            else
            {
                System.Web.HttpContext.Current.Request.Cookies["Load"].Value = "start";
                System.Web.HttpContext.Current.Request.Cookies["Load"].Path = "/";
                System.Web.HttpContext.Current.Response.AppendCookie(System.Web.HttpContext.Current.Request.Cookies["Load"]);
            }

            string html = htmlStr;
            System.Web.HttpContext.Current.Response.Write(htmlStr);
            System.Web.HttpContext.Current.Response.Buffer = false;

            System.Web.HttpContext.Current.Request.Cookies["Load"].Value = "end";
            System.Web.HttpContext.Current.Request.Cookies["Load"].Path = "/";
            System.Web.HttpContext.Current.Response.AppendCookie(System.Web.HttpContext.Current.Request.Cookies["Load"]);

            System.Web.HttpContext.Current.Response.Flush();
            System.Web.HttpContext.Current.Response.End();
        }

        public void toExecl2(GridView GridView1, string FileName, string SheetName, int STATE, string HidCol, bool NewSheet)
        {
            if (STATE == 0 || STATE == 3)
            {
                if (Request.Cookies["Load"] == null)
                {
                    HttpCookie cok = new HttpCookie("Load", "start");
                    cok.Path = "/";
                    Response.AppendCookie(cok);
                }
                else
                {
                    Request.Cookies["Load"].Value = "start";
                    Request.Cookies["Load"].Path = "/";
                    Response.AppendCookie(Request.Cookies["Load"]);
                }

                Response.ClearContent();
                Response.BufferOutput = true;
                Response.Charset = "utf-8";
                Response.ContentType = "text/xml";
                Response.ContentEncoding = System.Text.Encoding.UTF8;
                Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString() + ".xls");

                // 采用下面的格式，将兼容 Excel 2003,Excel 2007, Excel 2010。
                Response.Write("<?xml version='1.0'?><?mso-application progid='Excel.Sheet'?>");
                Response.Write(@"\r\n<Workbook xmlns='urn:schemas-microsoft-com:office:spreadsheet'
      xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel'
      xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet' xmlns:html='http://www.w3.org/TR/REC-html40'>");
                Response.Write(@"\r\n<DocumentProperties xmlns='urn:schemas-microsoft-com:office:office'>");
                Response.Write(@"\r\n<Author>测试</Author><LastAuthor>测试</LastAuthor>
      <Created>2017-03-02T23:44:00Z</Created><Company>thk</Company><Version>2017</Version>");
                Response.Write("\r\n</DocumentProperties>");
                Response.Write(@"\r\n<Styles><Style ss:ID='Default' ss:Name='Normal'><Alignment ss:Vertical='Center'/>
      <Borders/><Font ss:FontName='宋体' x:CharSet='134' ss:Size='14'/><Interior/><NumberFormat/><Protection/></Style>");

                //定义标题样式
                Response.Write(@"<Style ss:ID='Header'><Borders><Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>
         <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>
         <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>
         <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/></Borders>
         <Font ss:FontName='宋体' x:CharSet='134' ss:Size='14' ss:Color='Black' ss:Bold='1'/>
         <Alignment ss:Horizontal='Center'/></Style>");

                //定义边框
                Response.Write(@"<Style ss:ID='border'><NumberFormat ss:Format='@'/><Borders>
        <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>
        <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>
        <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>
        <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/></Borders></Style>");

                Response.Write("</Styles>");
                if (!NewSheet)
                {
                    Response.Write("\r\n<Worksheet ss:Name='" + FileName + "'>");
                    Response.Write("\r\n<Table>");
                }
            }

            if (GridView1.Rows.Count > 0)
            {
                //计算该 Sheet 中的数据起始行和结束行。
                if (NewSheet)
                {
                    Response.Write("\r\n<Worksheet ss:Name='" + SheetName + "'>");
                    Response.Write("\r\n<Table>");
                }

                int hidcolCount = 0;
                string[] hidcol = HidCol.Split(',');
                if (!string.IsNullOrEmpty(HidCol))
                {
                    hidcolCount = hidcol.Length;
                }

                //输出标题
                Response.Write("\r\n<Row ss:AutoFitHeight='1'>");
                Response.Write("<Cell ss:StyleID='Header'  ss:MergeAcross='" + (GridView1.HeaderRow.Cells.Count - hidcolCount - 1) + "'><Data ss:Type='String'>" + SheetName + "</Data></Cell>");
                Response.Write("\r\n</Row>");

                //表头
                Response.Write("\r\n<Row ss:AutoFitHeight='1'>");
                for (int j = 0; j < GridView1.HeaderRow.Cells.Count; j++)
                {
                    bool insert = true;
                    for (int h = 0; h < hidcolCount; h++)
                    {
                        if (j.ToString() == hidcol[h])
                        {
                            insert = false;
                        }
                    }
                    if (insert)
                    {
                        Response.Write("<Cell ss:StyleID='Header'  ss:MergeAcross='" + 0 + "' ss:MergeDown='" + 0 + "'><Data ss:Type='String'>" + GridView1.HeaderRow.Cells[j].Text + "</Data></Cell>");
                    }
                }
                Response.Write("\r\n</Row>");

                for (int j = 0; j < GridView1.Rows.Count; j++)
                {
                    Response.Write("\r\n<Row>");
                    for (int c = 0; c < GridView1.HeaderRow.Cells.Count; c++)
                    {
                        bool insert = true;
                        for (int h = 0; h < hidcolCount; h++)
                        {
                            if (c.ToString() == hidcol[h])
                            {
                                insert = false;
                            }
                        }

                        if (insert)
                        {
                            int gr = GridView1.Rows[j].Cells[c].RowSpan - 1;
                            int gc = GridView1.Rows[j].Cells[c].ColumnSpan - 1;

                            if (GridView1.Rows[j].Cells[c].RowSpan == 0)
                            {
                                gr = 0;
                            }
                            if (GridView1.Rows[j].Cells[c].ColumnSpan == 0)
                            {
                                gc = 0;
                            }

                            Response.Write("<Cell ss:StyleID='border' ss:MergeAcross='" + gc + "' ss:MergeDown='" + gr + "'><Data ss:Type='String'>" + GridView1.Rows[j].Cells[c].Text + "</Data></Cell>");
                        }
                    }
                    Response.Write("\r\n</Row>");
                }

                if (NewSheet || STATE == 2)
                {
                    Response.Write("\r\n</Table>");
                    Response.Write("\r\n</Worksheet>");
                }
            }

            if (STATE == 2 || STATE == 3)
            {
                if (!NewSheet)
                {
                    Response.Write("\r\n</Table>");
                    Response.Write("\r\n</Worksheet>");
                }

                Response.Write("\r\n</Workbook>");

                Request.Cookies["Load"].Value = "end";
                Request.Cookies["Load"].Path = "/";
                Response.AppendCookie(Request.Cookies["Load"]);

                Response.Flush();
                Response.End();
            }
        }

        private Microsoft.Office.Interop.Excel.Application app = null;
        private Microsoft.Office.Interop.Excel.Workbook workBook = null;
        private Microsoft.Office.Interop.Excel.Worksheet worksheet = null;
        private Microsoft.Office.Interop.Excel.Range range = null;

        public void toExecl3(GridView GridView1, string FileName, string SheetName, int STATE, string HidCol, bool NewSheet)
        {
            int r = 2;

            if (STATE == 0 || STATE == 3)
            {
                if (Request.Cookies["Load"] == null)
                {
                    HttpCookie cok = new HttpCookie("Load", "start");
                    cok.Path = "/";
                    Response.AppendCookie(cok);
                }
                else
                {
                    Request.Cookies["Load"].Value = "start";
                    Request.Cookies["Load"].Path = "/";
                    Response.AppendCookie(Request.Cookies["Load"]);
                }

                app = new Microsoft.Office.Interop.Excel.Application();
                app.Visible = false;
                workBook = app.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);

                if (!NewSheet)
                {
                    workBook.Sheets.Add(System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
                    worksheet = workBook.Sheets[1];
                    worksheet.Name = FileName;
                    startrow = 0;
                }
            }

            if (GridView1.Rows.Count > 0)
            {
                if (NewSheet)
                {
                    workBook.Sheets.Add(System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
                    worksheet = workBook.Sheets[1];
                    worksheet.Name = SheetName;
                }

                //设置列
                decimal gvwidth = 0;
                int h = 0;
                for (int k = 0; k < GridView1.HeaderRow.Cells.Count; k++)
                {
                    if (("," + HidCol + ",").IndexOf("," + k + ",") < 0)
                    {
                        gvwidth += decimal.Parse(GridView1.Columns[k].ItemStyle.Width.ToString().Replace('%', ' '));
                    }
                }
                for (int j = 0; j < GridView1.HeaderRow.Cells.Count; j++)
                {
                    if (("," + HidCol + ",").IndexOf("," + j + ",") < 0)
                    {
                        worksheet.Cells[r + startrow, j + 1 - h] = GridView1.HeaderRow.Cells[j].Text;
                        range = worksheet.get_Range(GetABC(j + 1 - h) + (1 + startrow), GetABC(j + 1 - h) + (r + startrow));
                        range.ColumnWidth = (decimal.Parse(GridView1.Columns[j].ItemStyle.Width.ToString().Replace('%', ' ')) / gvwidth) * 200;
                    }
                    else
                    {
                        h++;
                    }
                }
                range = worksheet.get_Range("A" + (r + startrow), GetABC(GridView1.HeaderRow.Cells.Count - h) + (r + startrow));
                range.Font.Bold = true; //粗体
                range.Font.Size = 12;//字体大小
                range.Font.Name = "微软雅黑";//字体
                range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //居中

                for (int i = 0; i < GridView1.Rows.Count; i++)
                {
                    h = 0;
                    for (int j = 0; j < GridView1.Columns.Count; j++)
                    {
                        if (("," + HidCol + ",").IndexOf("," + j + ",") < 0)
                        {
                            if (GridView1.Rows[i].Cells[j].Text.Trim() != "&nbsp;")
                            {
                                worksheet.Cells[i + 1 + r + startrow, j + 1 - h] = GridView1.Rows[i].Cells[j].Text;
                            }
                            else
                            {
                                worksheet.Cells[i + 1 + r + startrow, j + 1 - h] = "";
                            }

                            if (GridView1.Rows[i].Cells[j].RowSpan != 0 || GridView1.Rows[i].Cells[j].ColumnSpan != 0)
                            {
                                if (GridView1.Rows[i].Cells[j].RowSpan != 0 && GridView1.Rows[i].Cells[j].ColumnSpan == 0)
                                {
                                    range = worksheet.get_Range(GetABC(j + 1 - h) + (i + 1 + r + startrow), GetABC(j + 1 - h) + (i + r + GridView1.Rows[i].Cells[j].RowSpan + startrow));
                                    range.Merge(0);
                                }
                                else if (GridView1.Rows[i].Cells[j].ColumnSpan != 0 && GridView1.Rows[i].Cells[j].RowSpan == 0)
                                {
                                    range = worksheet.get_Range(GetABC(j + 1 - h) + (i + 1 + r + startrow), GetABC(j + GridView1.Rows[i].Cells[j].ColumnSpan - h) + (i + 1 + r + startrow));
                                    range.Merge(0);
                                }
                            }
                        }
                        else
                        {
                            h++;
                        }
                    }
                }
                range = worksheet.get_Range("A" + (r + startrow), GetABC(GridView1.HeaderRow.Cells.Count - h) + (GridView1.Rows.Count + r + startrow));
                range.Font.Size = 10;//字体大小
                range.Font.Name = "微软雅黑";//字体
                range.WrapText = true;
                range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;

                //设置标题
                worksheet.Cells[1, 1] = SheetName;
                range = worksheet.get_Range("A" + (1 + startrow), GetABC(GridView1.HeaderRow.Cells.Count - h) + (1 + startrow));
                range.Merge(0);
                range.Font.Bold = true; //粗体
                range.Font.Size = 12;//字体大小
                range.Font.Name = "微软雅黑";//字体
                range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //居中

                if (!NewSheet)
                {
                    startrow += r + GridView1.Rows.Count;
                }
            }

            if (STATE == 2 || STATE == 3)
            {
                string dir = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory);
                File.Delete(dir + "\\" + FileName + ".xls");
                workBook.SaveAs(dir + "\\" + FileName + ".xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing);
                workBook.Close(false, Type.Missing, Type.Missing);
                app.Quit();

                Request.Cookies["Load"].Value = "end";
                Request.Cookies["Load"].Path = "/";
                Response.AppendCookie(Request.Cookies["Load"]);

                startrow = 0;
            }
        }

        private HSSFWorkbook WorkBook = null;
        private ISheet sheet = null;

        public void toExecl4(GridView GridView1, string FileName, string SheetName, int STATE, string HidCol, bool NewSheet)
        {
            int r = 2;

            if (STATE == 0 || STATE == 3)
            {
                if (Request.Cookies["Load"] == null)
                {
                    HttpCookie cok = new HttpCookie("Load", "start");
                    cok.Path = "/";
                    Response.AppendCookie(cok);
                }
                else
                {
                    Request.Cookies["Load"].Value = "start";
                    Request.Cookies["Load"].Path = "/";
                    Response.AppendCookie(Request.Cookies["Load"]);
                }

                WorkBook = new HSSFWorkbook();

                if (!NewSheet)
                {
                    sheet = WorkBook.CreateSheet(FileName);
                    startrow = 0;
                }
            }

            if (GridView1.Rows.Count > 0)
            {
                if (NewSheet)
                {
                    sheet = WorkBook.CreateSheet(SheetName);
                }

                //设置列
                var headerRow = sheet.CreateRow(1 + startrow);
                ICellStyle headStyle = WorkBook.CreateCellStyle();
                headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;// 左右居中
                headStyle.VerticalAlignment = VerticalAlignment.Center;// 上下居中
                headStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                headStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                headStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                headStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                IFont fonth = WorkBook.CreateFont();
                fonth.FontHeightInPoints = 12;
                fonth.FontName = "微软雅黑";
                fonth.Boldweight = 700;
                headStyle.SetFont(fonth);
                decimal gvwidth = 0;
                int h = 0;
                for (int k = 0; k < GridView1.HeaderRow.Cells.Count; k++)
                {
                    if (("," + HidCol + ",").IndexOf("," + k + ",") < 0)
                    {
                        gvwidth += decimal.Parse(GridView1.Columns[k].ItemStyle.Width.ToString().Replace('%', ' '));
                    }
                }
                for (int j = 0; j < GridView1.HeaderRow.Cells.Count; j++)
                {
                    if (("," + HidCol + ",").IndexOf("," + j + ",") < 0)
                    {
                        headerRow.CreateCell(j - h).SetCellValue(GridView1.HeaderRow.Cells[j].Text);
                        headerRow.GetCell(j - h).CellStyle = headStyle;
                        sheet.SetColumnWidth(j - h, int.Parse(((decimal.Parse(GridView1.Columns[j].ItemStyle.Width.ToString().Replace('%', ' ')) / gvwidth) * 200 * 256).ToString().Split('.')[0]));
                    }
                    else
                    {
                        h++;
                    }
                }

                ICellStyle rowStyle = WorkBook.CreateCellStyle();
                rowStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;// 左右居中
                rowStyle.VerticalAlignment = VerticalAlignment.Center;// 上下居中
                rowStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                rowStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                rowStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                rowStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                IFont fontr = WorkBook.CreateFont();
                fontr.FontHeightInPoints = 10;
                fonth.FontName = "微软雅黑";
                rowStyle.SetFont(fontr);
                for (int i = 0; i < GridView1.Rows.Count; i++)
                {
                    h = 0;
                    IRow RowBody = sheet.CreateRow(i + r + startrow);
                    for (int j = 0; j < GridView1.Columns.Count; j++)
                    {
                        if (("," + HidCol + ",").IndexOf("," + j + ",") < 0)
                        {
                            if (GridView1.Rows[i].Cells[j].Text.Trim() != "&nbsp;")
                            {
                                RowBody.CreateCell(j - h).SetCellValue(GridView1.Rows[i].Cells[j].Text);
                            }
                            else
                            {
                                RowBody.CreateCell(j - h).SetCellValue("");
                            }
                            RowBody.GetCell(j - h).CellStyle = rowStyle;

                            if (GridView1.Rows[i].Cells[j].RowSpan != 0 || GridView1.Rows[i].Cells[j].ColumnSpan != 0)
                            {
                                if (GridView1.Rows[i].Cells[j].RowSpan != 0 && GridView1.Rows[i].Cells[j].ColumnSpan == 0)
                                {
                                    sheet.AddMergedRegion(new CellRangeAddress(i + r + startrow, i + r + GridView1.Rows[i].Cells[j].RowSpan - 1 + startrow, j - h, j - h));
                                }
                                else if (GridView1.Rows[i].Cells[j].ColumnSpan != 0 && GridView1.Rows[i].Cells[j].RowSpan == 0)
                                {
                                    sheet.AddMergedRegion(new CellRangeAddress(i + r + startrow, i + r + startrow, j - h, j + GridView1.Rows[i].Cells[j].ColumnSpan - h - 1));
                                }
                            }
                        }
                        else
                        {
                            h++;
                        }
                    }
                }

                //设置标题
                var titleRow = sheet.CreateRow(0 + startrow);
                ICellStyle titleStyle = WorkBook.CreateCellStyle();
                titleStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;// 左右居中
                titleStyle.VerticalAlignment = VerticalAlignment.Center;// 上下居中
                IFont fontt = WorkBook.CreateFont();
                fontt.FontHeightInPoints = 12;
                fontt.FontName = "微软雅黑";
                fontt.Boldweight = 700;
                titleStyle.SetFont(fontt);
                titleRow.CreateCell(0).SetCellValue(SheetName);
                titleRow.GetCell(0).CellStyle = titleStyle;
                sheet.AddMergedRegion(new CellRangeAddress(0 + startrow, 0 + startrow, 0, GridView1.HeaderRow.Cells.Count - h - 1));

                if (!NewSheet)
                {
                    startrow += r + GridView1.Rows.Count;
                }
            }

            if (STATE == 2 || STATE == 3)
            {
                //string dir = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory);
                string path = "D:" + "\\" + FileName + ".xls";
                if (File.Exists(path))
                {
                    File.Delete(path);
                }
                using (FileStream file = new FileStream(path, FileMode.Create))
                {
                    WorkBook.Write(file);
                    file.Close();
                }

                Request.Cookies["Load"].Value = "end";
                Request.Cookies["Load"].Path = "/";
                Response.AppendCookie(Request.Cookies["Load"]);

                startrow = 0;
            }
        }

        //myxls

        #endregion 导出Excel

        #region 导入Excel

        public string GetExcelFirstTableName(string fullPath)
        {
            string tableName = null;
            string strConn = "";

            if (File.Exists(fullPath))
            {
                if (ExcelWay == "2003")
                {
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fullPath + ";Extended Properties='Excel 8.0; HDR=No; IMEX=1'";
                }
                else
                {
                    strConn = "Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + fullPath + ";Extended Properties='Excel 12.0; HDR=No; IMEX=1'";
                }

                using (OleDbConnection conn = new OleDbConnection(strConn))
                {
                    conn.Open();
                    tableName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0][2].ToString().Trim();
                }
            }
            return tableName;
        }

        public DataSet ExcelToDataSet(string fullPath, string TSql)
        {
            DataSet ds;
            string strCon = "";
            if (ExcelWay == "2003")
            {
                //2003
                strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fullPath + ";Extended Properties='Excel 8.0; HDR=No; IMEX=1'";
            }
            else
            {
                //2007
                strCon = "Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + fullPath + ";Extended Properties='Excel 12.0; HDR=No; IMEX=1'";
            }
            OleDbConnection myConn = new OleDbConnection(strCon);
            string strCom = TSql;
            myConn.Open();
            OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
            ds = new DataSet();
            myCommand.Fill(ds);
            myConn.Close();
            return ds;
        }

        public int Update(string filePath, object[] parm, string sql)
        {
            bool canOpen = false;

            OleDbConnection conn;
            if (ExcelWay == "2003")
            {
                conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0; HDR=No; IMEX=1'");
            }
            else
            {
                conn = new OleDbConnection("Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0;HDR=No;IMEX=1'");
            }

            try//尝试数据连接是否可用
            {
                conn.Open();
                canOpen = true;
            }
            catch { }

            if (canOpen)
            {
                try
                {
                    OleDbParameter[] parms = new OleDbParameter[parm.Length];
                    OleDbCommand cmd = new OleDbCommand(sql, conn);
                    for (int i = 0; i < parm.Length; i++)
                    {
                        parms[i] = new OleDbParameter("arg" + i.ToString(), OleDbType.VarChar, 1000);
                        parms[i].Direction = ParameterDirection.Input;
                        parms[i].Value = parm[i];
                        cmd.Parameters.Add(parms[i]);
                    }
                    int result = cmd.ExecuteNonQuery();
                    return result;
                }
                catch
                {
                }
            }

            conn.Close();
            return 0;
        }

        public DataTable ExcelToDataSet(string FilePath)
        {
            //第一行一般为标题行。
            DataTable table = new DataTable();
            //根据路径通过已存在的excel来创建HSSFWorkbook，即整个excel文档
            HSSFWorkbook workbook = new HSSFWorkbook(File.Open(FilePath, FileMode.Open));
            ISheet sheet = (ISheet)workbook.GetSheetAt(0);
            //获取excel的第一个sheet

            //获取Excel的最大行数
            int rowsCount = sheet.PhysicalNumberOfRows;
            //为保证Table布局与Excel一样，这里应该取所有行中的最大列数（需要遍历整个Sheet）。
            //为少一交全Excel遍历，提高性能，我们可以人为把第0行的列数调整至所有行中的最大列数。
            int colsCount = sheet.GetRow(0).PhysicalNumberOfCells;

            for (int i = 0; i < colsCount; i++)
            {
                table.Columns.Add(i.ToString());
            }

            for (int x = 0; x < rowsCount; x++)
            {
                DataRow dr = table.NewRow();
                for (int y = 0; y < colsCount; y++)
                {
                    dr[y] = sheet.GetRow(x).GetCell(y).ToString();
                }
                table.Rows.Add(dr);
            }

            sheet = null;
            workbook = null;
            return table;
        }

        #endregion 导入Excel

        #region world导出

        public void Download()
        {
            Random rd = new Random();
            string fileName = DateTime.Now.ToString("yyyyMMddhhmm") + rd.Next() + ".doc";
            //存储路径
            string path = Server.MapPath(fileName);
            //创建字符输出流
            StreamWriter sw = new StreamWriter(path, true, System.Text.UnicodeEncoding.UTF8);
            //需要导出的内容
            // string str = "<html><head><title>无标题文档</title></head><body>这里放从数据库导出的word文档内容</body></html>";
            string str = "";
            str += "<html><head><title>无标题文档</title></head><body>";
            str += "<div>阅读报表</div>";
            str += "<table border='1'><tr>";
            str += "<td>20000</td>";
            str += "<td>10000</td></tr><tr>";
            str += "<td>30000</td>";
            str += "<td>30000</td><tr>";
            str += "</table></body></html>";
            //写入
            sw.Write(str);
            sw.Close();
            Response.Clear();
            Response.Buffer = true;
            this.EnableViewState = false;
            Response.Charset = "utf-8";
            Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
            Response.ContentType = "application/octet-stream";
            Response.WriteFile(path);
            Response.Flush();
            Response.Close();
            Response.End();
        }

        #endregion world导出

        #region 转excel为html

        public void Excel2Html(string path, string savePath)
        {
            try
            {
                File.Delete(savePath);

                string str = string.Empty;
                Microsoft.Office.Interop.Excel.Application repExcel = new Microsoft.Office.Interop.Excel.Application();
                Microsoft.Office.Interop.Excel.Workbook workbook = null;
                workbook = repExcel.Application.Workbooks.Open(path, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                object htmlFile = savePath;
                object ofmt = Microsoft.Office.Interop.Excel.XlFileFormat.xlHtml;
                workbook.SaveAs(htmlFile, ofmt, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                object osave = false;
                workbook.Close(osave, Type.Missing, Type.Missing);
                repExcel.Quit();
            }
            catch { }
        }

        public void ExcelToHtml(string path, string savePath)
        {
            try
            {
                File.Delete(savePath);

                ExcelToHtmlConverter excelToHtmlConverter = new ExcelToHtmlConverter();
                excelToHtmlConverter.OutputColumnHeaders = false;
                excelToHtmlConverter.OutputHiddenColumns = false;
                excelToHtmlConverter.OutputHiddenRows = false;
                excelToHtmlConverter.OutputLeadingSpacesAsNonBreaking = false;
                excelToHtmlConverter.UseDivsToSpan = false;
                excelToHtmlConverter.OutputRowNumbers = false;

                Stream fs = File.OpenRead(path);
                HSSFWorkbook workbook = new HSSFWorkbook(fs);
                excelToHtmlConverter.ProcessWorkbook(workbook);

                excelToHtmlConverter.Document.Save(savePath);
            }
            catch { }
        }

        #endregion 转excel为html

        public string GetABC(int Number)
        {
            if (Number == 1)
            {
                return "A";
            }
            else if (Number == 2)
            {
                return "B";
            }
            else if (Number == 3)
            {
                return "C";
            }
            else if (Number == 4)
            {
                return "D";
            }
            else if (Number == 5)
            {
                return "E";
            }
            else if (Number == 6)
            {
                return "F";
            }
            else if (Number == 7)
            {
                return "G";
            }
            else if (Number == 8)
            {
                return "H";
            }
            else if (Number == 9)
            {
                return "I";
            }
            else if (Number == 10)
            {
                return "J";
            }
            else if (Number == 11)
            {
                return "K";
            }
            else if (Number == 12)
            {
                return "L";
            }
            else if (Number == 13)
            {
                return "M";
            }
            else if (Number == 14)
            {
                return "N";
            }

            return "A";
        }

        public void KillExcel()
        {
            try
            {
                #region 强行杀死最近打开的Excel进程

                System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL");
                System.DateTime startTime = new DateTime();
                int m, killID = 0;
                for (m = 0; m < excelProc.Length; m++)
                {
                    if (startTime < excelProc[m].StartTime)
                    {
                        startTime = excelProc[m].StartTime;
                        killID = m;
                    }
                }
                if (excelProc[killID].HasExited == false)
                {
                    excelProc[killID].Kill();
                }

                #endregion 强行杀死最近打开的Excel进程
            }
            catch { }
        }

        public void MergeRow(GridView gv, string cols, int startRow, int endRow)
        {
            DataTable dt = (DataTable)gv.DataSource;
            for (int rowIndex = endRow; rowIndex >= startRow; rowIndex--)
            {
                GridViewRow currentRow = gv.Rows[rowIndex];
                GridViewRow prevRow = gv.Rows[rowIndex + 1];

                DataRow dr = dt.Rows[rowIndex];
                DataRow dr2 = dt.Rows[rowIndex + 1];
                string[] Cols = cols.Split(',');

                for (int i = 0; i < Cols.Length; i++)
                {
                    int currentCol = int.Parse(Cols[i].ToString());

                    if (!string.IsNullOrEmpty(dr[currentCol].ToString()) && !string.IsNullOrEmpty(dr2[currentCol].ToString()))
                    {
                        if (dr[currentCol].ToString() == dr2[currentCol].ToString())
                        {
                            currentRow.Cells[currentCol].RowSpan = prevRow.Cells[currentCol].RowSpan < 1 ? 2 : prevRow.Cells[currentCol].RowSpan + 1;
                            prevRow.Cells[currentCol].RowSpan = 0;
                            prevRow.Cells[currentCol].Visible = false;
                        }
                    }
                }
            }
        }

        public void MergeRowTs(GridView gv, string cols, int startRow, int endRow, int State, int MergeRow, string Tag, int Col)
        {
            decimal xj = 0;
            decimal pre = 0;

            DataTable dt = (DataTable)gv.DataSource;
            for (int rowIndex = endRow; rowIndex >= startRow; rowIndex--)
            {
                GridViewRow currentRow = gv.Rows[rowIndex];
                GridViewRow prevRow = gv.Rows[rowIndex + 1];

                DataRow dr = dt.Rows[rowIndex];
                DataRow dr2 = dt.Rows[rowIndex + 1];
                string[] Cols = cols.Split(',');

                for (int i = 0; i < Cols.Length; i++)
                {
                    int currentCol = int.Parse(Cols[i].ToString());

                    if (!string.IsNullOrEmpty(dr[currentCol].ToString()) && !string.IsNullOrEmpty(dr2[currentCol].ToString()))
                    {
                        if (currentCol == Col)
                        {
                            if (!string.IsNullOrEmpty(Tag))
                            {
                                if (State == 0)
                                {
                                    if (rowIndex != endRow)
                                    {
                                        xj += decimal.Parse(((Label)prevRow.FindControl(Tag + "1")).Text) + decimal.Parse(((Label)prevRow.FindControl(Tag + "2")).Text);

                                        pre = decimal.Parse(((Label)currentRow.FindControl(Tag + "1")).Text) + decimal.Parse(((Label)currentRow.FindControl(Tag + "2")).Text);
                                    }
                                }
                                else
                                {
                                    xj += decimal.Parse(((Label)prevRow.FindControl(Tag)).Text.Trim());
                                    pre = decimal.Parse(((Label)currentRow.FindControl(Tag)).Text.Trim());
                                }
                            }
                            else
                            {
                                if (State == 0)
                                {
                                    if (rowIndex != endRow)
                                    {
                                        xj += decimal.Parse(prevRow.Cells[8].Text) + decimal.Parse(prevRow.Cells[10].Text);

                                        pre = decimal.Parse(currentRow.Cells[8].Text) + decimal.Parse(currentRow.Cells[10].Text);
                                    }
                                }
                                else
                                {
                                    xj += decimal.Parse(prevRow.Cells[MergeRow].Text.Trim());
                                    pre = decimal.Parse(currentRow.Cells[MergeRow].Text.Trim());
                                }
                            }
                        }

                        if (dr[currentCol].ToString() == dr2[currentCol].ToString())
                        {
                            if (currentCol == Col)
                            {
                                currentRow.Cells[MergeRow].RowSpan = prevRow.Cells[MergeRow].RowSpan < 1 ? 2 : prevRow.Cells[MergeRow].RowSpan + 1;
                                prevRow.Cells[MergeRow].RowSpan = 0;
                                prevRow.Cells[MergeRow].Visible = false;
                            }

                            currentRow.Cells[currentCol].RowSpan = prevRow.Cells[currentCol].RowSpan < 1 ? 2 : prevRow.Cells[currentCol].RowSpan + 1;
                            prevRow.Cells[currentCol].RowSpan = 0;
                            prevRow.Cells[currentCol].Visible = false;
                        }
                        else
                        {
                            if (currentCol == Col)
                            {
                                if (!string.IsNullOrEmpty(Tag))
                                {
                                    if (State == 0)
                                    {
                                        ((Label)prevRow.FindControl("lblXj")).Text = xj.ToString();
                                        xj = 0;
                                    }
                                    else
                                    {
                                        ((Label)prevRow.FindControl(Tag)).Text = xj.ToString();
                                        xj = 0;
                                    }
                                }
                                else
                                {
                                    if (State == 0)
                                    {
                                        prevRow.Cells[12].Text = xj.ToString();
                                        xj = 0;
                                    }
                                    else
                                    {
                                        prevRow.Cells[MergeRow].Text = xj.ToString();
                                        xj = 0;
                                    }
                                }
                            }
                        }

                        if (rowIndex == startRow)
                        {
                            if (currentCol == Col)
                            {
                                if (!string.IsNullOrEmpty(Tag))
                                {
                                    if (State == 0)
                                    {
                                        xj += pre;
                                        ((Label)currentRow.FindControl("lblXj")).Text = xj.ToString();
                                    }
                                    else
                                    {
                                        xj += pre;
                                        ((Label)currentRow.FindControl(Tag)).Text = xj.ToString();
                                    }
                                }
                                else
                                {
                                    if (State == 0)
                                    {
                                        xj += pre;
                                        currentRow.Cells[12].Text = xj.ToString();
                                    }
                                    else
                                    {
                                        xj += pre;
                                        currentRow.Cells[MergeRow].Text = xj.ToString();
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }

        public void GroupRow(GridView gridView, int rows, int sCol, int eCol)
        {
            TableCell oldTc = gridView.Rows[rows].Cells[sCol];
            for (int i = 1; i < eCol - sCol; i++)
            {
                TableCell tc = gridView.Rows[rows].Cells[i + sCol];
                tc.ColumnSpan = 0;
                tc.RowSpan = 0;
                tc.Visible = false;
                if (oldTc.ColumnSpan == 0)
                {
                    oldTc.ColumnSpan = 1;
                }
                oldTc.ColumnSpan++;
                oldTc.RowSpan = 0;
            }
        }

        //#region sheet导出
        ///// <summary>
        ///// 导出多 Sheet 报表
        ///// </summary>
        ///// <param name="ds">要导出的数据</param>
        //public void DataToExcle(System.Data.DataSet ds)
        //{
        //    if (ds.Tables == null||ds.Tables.Count==0)
        //    {
        //        MessageBox.Show(Page,"无数据！");
        //        return;
        //    }
        //    SaveFileDialog saveFileDialog = new SaveFileDialog();
        //    saveFileDialog.DefaultExt = "xlsx";
        //    saveFileDialog.Title = "导出数据到Execl表格";
        //    //if (ds.Tables.Count == 12)
        //    //    saveFileDialog.FileName = "月度报表";
        //    //if (ds.Tables.Count == 4)
        //    //    saveFileDialog.FileName = "季度报表";
        //    //if (ds.Tables.Count == 1)
        //    //    saveFileDialog.FileName = "年度报表";
        //    saveFileDialog.Filter = "microsoft office execl files (*.xlsx)|*.xlsx";
        //    saveFileDialog.RestoreDirectory = true;

        //    if (saveFileDialog.ShowDialog() == DialogResult.OK)
        //    {
        //        System.Reflection.Missing miss = System.Reflection.Missing.Value;
        //        Microsoft.Office.Interop.Excel.Application xlapp = new Microsoft.Office.Interop.Excel.Application();
        //        xlapp.Visible = true;
        //        Workbooks workbooks = xlapp.Workbooks;
        //        Workbook workbook = workbooks.Add(true);
        //        if (ds.Tables.Count > 1)
        //        {
        //            workbook.Sheets.Add(miss,workbook.Sheets[1], ds.Tables.Count - 1, miss);
        //        }

        //        for (int d = 0; d < ds.Tables.Count; d++)
        //        {
        //            Worksheet ws = workbook.Worksheets[d + 1] as Worksheet;
        //            if (ds.Tables.Count == 12)
        //                ws.Name = d + 1 + "月份";
        //            if (ds.Tables.Count == 4)
        //                ws.Name = d + 1 + "季度";
        //            if (ds.Tables.Count == 1)
        //                ws.Name = "年度";
        //            int totalCellsCount = ds.Tables[d].Columns.Count;
        //            Range range = ws.Range[ws.Cells[1, 1], ws.Cells[1, ds.Tables[d].Columns.Count]];
        //            //生成列
        //            for (int i = 0; i < ds.Tables[d].Columns.Count; i++)
        //            {
        //                range.Cells[1, i + 1] = ds.Tables[d].Columns[i].ColumnName;
        //                range.Interior.ColorIndex = 15;
        //                range.Font.Bold = true;
        //                range.Font.Size = 10;
        //                range.EntireColumn.AutoFit();
        //                range.EntireRow.AutoFit();
        //            }

        //            //插入数据
        //            for (int r = 0; r < ds.Tables[d].Rows.Count; r++)
        //            {
        //                for (int i = 0; i < ds.Tables[d].Columns.Count; i++)
        //                {
        //                    ws.Cells[r + 2, i + 1] = "'" + ds.Tables[d].Rows[r][i].ToString();
        //                }
        //            }
        //            System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
        //            range = null;
        //            System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
        //            ws = null;
        //        }
        //        ((Worksheet)workbook.Sheets[1]).Select(true);//选中第一个worksheet
        //        workbook.Close(true, miss, miss);//关闭workbook, 并保存对workbook的所有修改

        //        System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
        //        workbook = null;
        //        xlapp.Quit();
        //        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlapp);
        //        xlapp = null;
        //        GC.Collect();
        //        GC.WaitForPendingFinalizers();
        //    }
        //}

        //#endregion
    }

    #region 将List转换成DataTable

    /// <summary>
    /// 方法一
    /// </summary>
    public static class ExtensionMethods
    {
        /// <summary>
        /// 将List转换成DataTable
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="data"></param>
        /// <returns></returns>
        public static DataTable ToDataTable<T>(this IList<T> data)
        {
            PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
            DataTable dt = new DataTable();
            try
            {
                for (int i = 0; i < properties.Count; i++)
                {
                    PropertyDescriptor property = properties[i];
                    dt.Columns.Add(property.Name, property.PropertyType);
                }
                object[] values = new object[properties.Count];
                foreach (T item in data)
                {
                    for (int i = 0; i < values.Length; i++)
                    {
                        values[i] = properties[i].GetValue(item);
                    }
                    dt.Rows.Add(values);
                }
            }
            catch (Exception ex)
            {
                //MessageBox.Show(this, ex.Message);
            }
            return dt;
        }
    }

    /// <summary>
    /// 方法二
    /// </summary>
    public static class Transformation
    {
        /// <summary>
        /// list转换为DataTable
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="array">The array.</param>
        /// <returns>DataTable.</returns>
        public static DataTable CopyToDataTable<T>(this IEnumerable<T> array)
        {
            var ret = new DataTable();
            foreach (PropertyDescriptor dp in TypeDescriptor.GetProperties(typeof(T)))
                ret.Columns.Add(dp.Name);
            foreach (T item in array)
            {
                var Row = ret.NewRow();
                foreach (PropertyDescriptor dp in TypeDescriptor.GetProperties(typeof(T)))
                    Row[dp.Name] = dp.GetValue(item);
                ret.Rows.Add(Row);
            }
            return ret;
        }
    }

    #endregion 将List转换成DataTable
}